﻿using aier.dataaccess.model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{

    public class AreaDal
    {
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 获取所有区域信息
        /// </summary>
        /// <returns>省，市，区/县，街道</returns>
        public List<AreaView> QueryAllArea()
        {
            List<AreaView> areas = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_AREA_INFO";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                areas = a.Query<AreaView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return areas;
        }

        /// <summary>
        /// 根据名称获取市ID
        /// </summary>
        /// <param name="new_name"></param>
        /// <returns></returns>
        public Guid GetAreaIdByName(string new_name)
        {
            Guid AreaId = Guid.Empty;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_areaId from new_areaBase /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(new_name))
                {
                    sqlBuilder.Where("new_name=@new_name");
                    sqlParams.Add("@new_name", new_name);
                }
                AreaId = a.ExecuteScalar<Guid>(sqlTemplate.RawSql, sqlParams);
            });

            return AreaId;
        }


        /// <summary>
        /// 根据ID获取区名称
        /// </summary>
        /// <param name="new_areaId"></param>
        /// <returns></returns>
        public string GetAreaNameById(string new_areaId)
        {

            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_name from new_areaBase /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(new_areaId))
                {
                    sqlBuilder.Where("new_areaId=@new_areaId");
                    sqlParams.Add("@new_areaId", new_areaId);
                }
                return a.ExecuteScalar<string>(sqlTemplate.RawSql, sqlParams);
            });

        }
    }
}
